 package org.firestorm.traficforecast.database;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.firestorm.traficforecast.utils.CommonUtils;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class NodeDAO {

    public static void main(String[] args) {
        File fXmlFile = new File(
                "E:\\HOC TAP\\KI 20141\\Project\\TAI LIEU\\map.osm");
        readData(fXmlFile);
        System.out.println(CommonUtils.distance(NodeDAO.getNode("2291046243"), NodeDAO.getNode("2291046233")));
    }

    public static void readData(File fXmlFile) {
        DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance();
        DocumentBuilder dBuilder;

        try {
            dBuilder = dbFactory.newDocumentBuilder();
            Document doc = dBuilder.parse(fXmlFile);
            doc.getDocumentElement().normalize();

            NodeList nodes = doc.getElementsByTagName("node");
            int len = nodes.getLength();
            for (int i = 0; i < len; i++) {
                Element node = (Element) nodes.item(i);
                String id = node.getAttribute("id");

                String name = getName(nodes.item(i));

                double lat = Double.parseDouble(node.getAttribute("lat"));
                double lon = Double.parseDouble(node.getAttribute("lon"));

                System.out.println(id + ", " + name + ", " + lat + ", " + lon);

                MyNode nodeToInsert = new MyNode(id, name, lat, lon, "");
                NodeDAO.insertNode(nodeToInsert);

            }
        } catch (ParserConfigurationException e) {
            e.printStackTrace();
        } catch (SAXException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static String getName(Node way) {
        Element e = (Element) way;
        NodeList tagList = e.getElementsByTagName("tag");

        for (int i = 0; i < tagList.getLength(); i++) {
            Node tag = tagList.item(i);
            Element eTag = (Element) tag;
            if ("name".equals(eTag.getAttribute("k"))) {
                return eTag.getAttribute("v");
            }
        }
        return "unknow";
    }

    @SuppressWarnings("unused")
    private static void createNodeTable() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        try {
            PreparedStatement stmt = conn
                    .prepareStatement("CREATE TABLE node(id VARCHAR(255), name VARCHAR(255), lat DOUBLE, lon DOUBLE) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci");
            stmt.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void insertNode(MyNode node) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("INSERT INTO node(id, name, lat, lon) VALUES(?, ?, ?, ?)");
            stmt.setString(1, node.getId());
            stmt.setString(2, node.getName());
            stmt.setDouble(3, node.getLat());
            stmt.setDouble(4, node.getLon());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    public static ArrayList<MyNode> listNode() {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        ArrayList<MyNode> result = new ArrayList<MyNode>();
        PreparedStatement stmt = null;
        try {
            stmt = conn
                    .prepareStatement("SELECT * FROM node");

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                Double lat = rs.getDouble("lat");
                Double lon = rs.getDouble("lon");
                MyNode node = new MyNode(id, name, lat, lon, "");
                result.add(node);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    
    // public static ArrayList<String> listID() {
    // MySQLConnect connect = new MySQLConnect("localhost:3306/investment",
    // "root", "123456");
    // Connection conn = connect.getConnection();
    // ArrayList<String> result = new ArrayList<String>();
    // try {
    // PreparedStatement stmt = conn
    // .prepareStatement("SELECT id FROM conclude");
    //
    // ResultSet rs = stmt.executeQuery();
    // while (rs.next()) {
    // String ids = rs.getString("id");
    // result.add(ids);
    // }
    // return result;
    // } catch (SQLException e) {
    // e.printStackTrace();
    // return null;
    // }
    // }
    //
    public static MyNode getNode(String id) {
    	MySQLConnect connect = new MySQLConnect();
    	Connection conn = connect.getConnection();
        MyNode node = null;
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM node WHERE id = '" + id
                    + "'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                node = new MyNode(rs.getString("id"), rs.getString("name"),
                        rs.getDouble("lat"), rs.getDouble("lon"), "");
            }
            return node;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }
    
    public static MyNode getNode(String id, Connection conn){
        MyNode node = null;
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("SELECT * FROM node WHERE id = '" + id
                    + "'");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                node = new MyNode(rs.getString("id"), rs.getString("name"),
                        rs.getDouble("lat"), rs.getDouble("lon"), "");
            }
            return node;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public static void deleteNode(String id) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement("DELETE FROM node WHERE id = ?");
            stmt.setString(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }

    public static void updateNode(MyNode node) {
        MySQLConnect connect = new MySQLConnect();
        Connection conn = connect.getConnection();
        PreparedStatement stmt = null;

        try {
            stmt = conn.prepareStatement("UPDATE node SET nextnode = ? WHERE id = ?");
            String nextNode = "";
            if (node.getNextNode() != null && node.getNextNode().size() > 0) {
                for (int i = 0; i < node.getNextNode().size(); i++) {
                    nextNode += node.getNextNode().get(i);
                }
            }
            stmt.setString(1, nextNode);
            stmt.setString(2, node.getId());

            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            MySQLConnect.closeConnection(conn, stmt);
        }
    }
}
